/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * touch by 
 */

package cn.jgls.bi.dataprocess;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.Iterator;
/**
 *
 * @author fish
 */
public class DPJDSaleHour extends JdbcDaoSupport implements DPDSaleHour {
    class getTsnSortIdRM implements RowMapper {

        @Override
    public Object mapRow(ResultSet rs, int index) throws SQLException {
            return rs.getInt(1);
        }
    }
    
    class setSaleHourSumRM implements RowMapper {
        @Override
    public Object mapRow(ResultSet rs, int index)
            throws SQLException {
            SaleSumHourMx SaleHourSum = new SaleSumHourMx();
            SaleHourSum.setSaleValue(new Float(rs.getFloat("xsje")));
            SaleHourSum.setJinhuo_value(new Float(rs.getFloat("jjje")));
            SaleHourSum.setHour(new String(rs.getString("hour")));
            return SaleHourSum;
        }
    }
    
    class setSaleWeekHzRM implements RowMapper {
        @Override
    public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            SaleSumWeekHz SSWH = new SaleSumWeekHz();
            SSWH.setSale_week_addup(new Float(rs.getFloat("sale_week_addup")));
            SSWH.setGrsprt_week_addup(new Float(rs.getFloat("grsprt_week_addup")));
            SSWH.setSale_week_amt(new Integer(rs.getInt("Sale_week_amt")));
            SSWH.setGrsprt_week_amt(new Integer(rs.getInt("Grsprt_week_amt")));
            return SSWH;
        }
    }
    class setSaleYearHzRM implements RowMapper {
        @Override
    public Object mapRow(ResultSet rs, int index)
            throws SQLException {
        SaleSumYearHz SSYH = new SaleSumYearHz();
        SSYH.setSale_year_addup(new Float(rs.getFloat("sale_year_addup")));
        SSYH.setGrsprt_year_addup(new Float(rs.getFloat("grsprt_year_addup")));
        SSYH.setSale_year_amt(new Integer(rs.getInt("Sale_year_amt")));
        SSYH.setGrsprt_year_amt(new Integer(rs.getInt("Grsprt_year_amt")));
        return SSYH;
    }
}
    @Override
    public List getTsnSortId() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.query("select id from t_bi_tsnsort where bj=1 order by id",new RowMapperResultReader(new getTsnSortIdRM()));
    }

    @Override
    public void SaleAmtHour(Date adTsnDate, int aiShopId, String asShopBm,int aiDateNo, String asYear, int aiYybh, int aiMan_id,String sTname){
//        System.out.println(adTsnDate+"***"+aiShopId);
        int iRc;
        JdbcTemplate jt = getJdbcTemplate();
        String sql="",sql1="";
        int TsnSort=0;
        Float SaleWeekAddup=0f,GrsprtWeekAddup=0f;
        Float SaleYearAddup=0f,GrsprtYearAddup=0f;
        int SaleWeekAmt=0,GrsprtWeekAmt=0;
        int SaleYearAmt=0,GrsprtYearAmt=0;
//        Calendar c = Calendar.getInstance();
//        c.setTime(adTsnDate);
//        c.add(Calendar.DATE,-1);
//        java.util.Date Yesterday=c.getTime();

        //该过程每天运行一次，提前取出所有的数据，供后面获取，所以目前定为2店，如2店闭店，请换为其他门店
        if (aiShopId == 2) {
            //清空临时表
            sql="delete from t_bi_sale_hour_mx_tmp";  //此处没有加tsn_sort标记，注意！！
            iRc = jt.update(sql);
            //全部商品
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,1 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and a.bm=b.bm and substr(lb,1,4)!='0829'  "+
                          " group by id,substr(a.time,1,2)" ;
            Object[] params = new Object[]{adTsnDate};
            int[] types = new int[]{Types.DATE};
            iRc = jt.update(sql, params, types);

            ////包装商品
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,2 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and (substr(lb,1,2) != '08' and substr(lb,1,2) != '19' and substr(lb,1,1) != '5' and substr(lb,1,1) != '6' ) " +
                          "   and a.bm=b.bm and substr(lb,1,4)!='0829'  "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            ////大肉
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,3 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and substr(lb,1,3)='089' " +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            //蛋
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,4 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='鸡蛋')" +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            //菜
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,5 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='蔬菜')" +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            //面食
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,6 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='面食')" +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
              //主食厨房
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,7 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_zscf_new09)" +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            //水果
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,8 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='水果')" +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            //牛羊肉
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,9 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in ('087631','087632','087644')" +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);
            //酱货
            sql="insert into t_bi_sale_hour_mx_tmp (tsn_id,xsje,jjje,tsn_sort,hour) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,10 as tsn_sort,substr(a.time,1,2) as hour    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (SELECT lb FROM sy_SXK WHERE PQBM='81008' OR PQBM='81009') " +
                          "   and a.bm=b.bm "+
                          " group by id,substr(a.time,1,2)" ;
            iRc = jt.update(sql, params, types);

        }

        sql="delete from t_bi_sale_hour_mx where tsn_date=? and tsn_id=?";  //此处没有加tsn_sort标记，注意！！
        Object[] params = new Object[]{adTsnDate,aiShopId};
        int[] types = new int[]{Types.DATE,Types.INTEGER};
        iRc = jt.update(sql, params, types);
//        System.out.println(adTsnDate+"***"+aiShopId+"***"+iRc);
        Iterator tskIter = getTsnSortId().iterator();  //得到要计算的是那个项目！
        while (tskIter.hasNext()) {
            switch((Integer)tskIter.next()){
                case 1:  //全部商品
                    TsnSort=1;
                    break;
                case 2:  //包装商品
                    TsnSort=2;
                    break;
                case 3:  //大肉
                    TsnSort=3;
                    break;
                case 4:  //蛋
                    TsnSort=4;
                    break;
                case 5:  //菜
                    TsnSort=5;
                    break;
                case 6:  //面食
                    TsnSort=6;
                    break;
                case 7:  //主食厨房
                    TsnSort=7;
                    break;
                case 8:  //水果
                    TsnSort=8;
                    break;
                case 9:  //牛羊肉
                    TsnSort=9;
                    break;
                case 10:  //酱货
                    TsnSort=10;
                    break;
             }
              sql = "select  xsje,jjje,hour " +
              "  from  t_bi_sale_hour_mx_tmp " +
              " where  tsn_id=? and tsn_sort="+TsnSort ;


//            System.out.println(TsnSort+"***"+aiShopId+"***"+adTsnDate+"***"+sql);
             Float MaoLiE=0f;
             SaleSumHourMx SSHM=new SaleSumHourMx();
             params = new Object[]{aiShopId};
             types = new int[]{Types.INTEGER};
             List query = jt.query(sql, params, new RowMapperResultReader(new setSaleHourSumRM()));
             Iterator tskIter1 = query.iterator();
             
             //将变量清零
             SaleWeekAddup=0f;
             GrsprtWeekAddup=0f;
             SaleWeekAmt=0;
             GrsprtWeekAmt=0;
             SaleYearAddup=0f;
             GrsprtYearAddup=0f;
             SaleYearAmt=0;
             GrsprtYearAmt=0;
             
             while(tskIter1.hasNext()){
                 SSHM=(SaleSumHourMx)tskIter1.next();
                 if (SSHM.getSaleValue()==0) {
                     MaoLiE=0f;}
                 else{ 
                     MaoLiE=SSHM.getSaleValue()-SSHM.getJinhuo_value();}
                 SaleWeekAddup=0f;
                 GrsprtWeekAddup=0f;
                 SaleWeekAmt=0;
                 GrsprtWeekAmt=0;
                 SaleYearAddup=0f;
                 GrsprtYearAddup=0f;
                 SaleYearAmt=0;
                 GrsprtYearAmt=0;
                 //周数据开始
                 sql1="select sale_week_addup,sale_week_amt, " + 
                      "       grsprt_week_addup,grsprt_week_amt " + 
                      "  from t_bi_sale_hour_mx " + 
                      " where tsn_date=(? - 7) " +
                      "   and tsn_sort=?  " + 
                      "   and tsn_id=? " + 
                      "   and tsn_year=?  " + 
                      "   and hour_period=? " + 
                      "   and tsn_date_no=? ";
                  SaleSumWeekHz SSWH=new SaleSumWeekHz();   //与周数据相同
                  params = new Object[]{adTsnDate,  TsnSort,aiShopId,Integer.parseInt(asYear),Integer.parseInt(SSHM.getHour()),aiDateNo};
                  types = new int[]{Types.DATE,Types.INTEGER,Types.INTEGER,Types.INTEGER,Types.INTEGER,Types.INTEGER};
                  query = jt.query(sql1, params, new RowMapperResultReader(new setSaleWeekHzRM()));
                  Iterator tskIter2 = query.iterator();
                  while(tskIter2.hasNext()){
                     SSWH=(SaleSumWeekHz)tskIter2.next();
                     SaleWeekAddup=SSWH.getSale_week_addup();             //周销售汇总
                     GrsprtWeekAddup=SSWH.getGrsprt_week_addup();         //周毛利汇总
                     SaleWeekAmt=SSWH.getSale_week_amt();                 //周销售次数汇总
                     GrsprtWeekAmt=SSWH.getGrsprt_week_amt();             //周毛利次数汇总
                  }
                  SaleWeekAddup=SaleWeekAddup+SSHM.getSaleValue();
                  GrsprtWeekAddup=GrsprtWeekAddup+MaoLiE;
                  SaleWeekAmt++;
                  GrsprtWeekAmt++;
                 //周数据结束
                  
                 //年数据开始
                  
                  sql1="select sale_year_addup,sale_year_amt,     " + 
                       "       grsprt_year_addup,grsprt_year_amt  " + 
                       "  from t_bi_sale_hour_mx	          " + 
                       " where tsn_date=(? - 1)    	  " +
                       "   and tsn_sort=? 			  " + 
                       "   and tsn_id=? 			  " + 
                       "   and tsn_year=?			  " + 
                       "   and hour_period=? ";
                  SaleSumYearHz SSYH=new SaleSumYearHz();
//                  params = new Object[]{Yesterday,aiShopId,TsnSort,Integer.parseInt(asYear),Integer.parseInt(SSHM.getHour())};
//                  types = new int[]{Types.DATE,Types.INTEGER,Types.INTEGER, Types.INTEGER,Types.INTEGER};
                  
                  params = new Object[]{adTsnDate,TsnSort, aiShopId,Integer.parseInt(asYear),Integer.parseInt(SSHM.getHour())};
                  types = new int[]{Types.DATE, Types.INTEGER, Types.INTEGER,Types.INTEGER,Types.INTEGER};
                  
                  query = jt.query(sql1, params, new RowMapperResultReader(new setSaleYearHzRM()));
                    Iterator tskIter3 = query.iterator();
                    while(tskIter3.hasNext()){
                        SSYH=(SaleSumYearHz)tskIter3.next();
                        SaleYearAddup=SSYH.getSale_year_addup();
                        GrsprtYearAddup=SSYH.getGrsprt_year_addup();
                        SaleYearAmt=SSYH.getSale_year_amt();
                        GrsprtYearAmt=SSYH.getGrsprt_year_amt();
                    }
                        SaleYearAddup=SaleYearAddup+SSHM.getSaleValue();
                        GrsprtYearAddup=GrsprtYearAddup+MaoLiE;
                        SaleYearAmt++;
                        GrsprtYearAmt++;
                 //年数据结束
                 sql="insert into t_bi_sale_hour_mx(TSN_DATE, " +
                                                   "TSN_ID, " +
                                                   "HOUR_PERIOD, " +
                                                   "SALE_VALUE, " +
                                                   "TSN_YEAR, " +
                                                   "TSN_DATE_NO, " +
                                                   "YYBH, " +
                                                   "MAN_ID," +
                                                   "TSN_SORT, " +
                                                   "SALE_GRSPRT," +
                                                   "sale_week_addup, " + 
                                                   "sale_week_amt, " + 
                                                   "grsprt_week_addup, " + 
                                                   "grsprt_week_amt, " +
                                                   "sale_year_addup, " +
                                                   "sale_year_amt, " +
                                                   "grsprt_year_addup, " +
                                                   "grsprt_year_amt) " +
                     "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                 params = new Object[]{adTsnDate,               aiShopId,      Integer.parseInt(SSHM.getHour()),SSHM.getSaleValue(),
                                       Integer.parseInt(asYear),aiDateNo,      aiYybh,                          aiMan_id,
                                       TsnSort,                 MaoLiE,        SaleWeekAddup,                   SaleWeekAmt,   
                                       GrsprtWeekAddup,         GrsprtWeekAmt, SaleYearAddup,                   SaleYearAmt,   
                                       GrsprtYearAddup,         GrsprtYearAmt};
                 types = new int[]{    Types.DATE,              Types.INTEGER, Types.INTEGER,                   Types.FLOAT,
                                       Types.INTEGER,           Types.INTEGER, Types.INTEGER,                   Types.INTEGER,
                                       Types.INTEGER,           Types.FLOAT ,  Types.FLOAT,                     Types.INTEGER, 
                                       Types.FLOAT,             Types.INTEGER, Types.FLOAT,                     Types.INTEGER, 
                                       Types.FLOAT,             Types.INTEGER};
                 iRc = jt.update(sql, params, types);
                 //System.out.println(aiShopId+"****"+SSHM.getSaleValue()+"****"+TsnSort);
                 System.out.println("now process hour data,shopid:"+aiShopId+" Tsnsort:"+TsnSort+" value:"+SSHM.getSaleValue());
             }
        }
        //System.out.println(adTsnDate.toString()+"----运行完毕！");
    }
    
}
